﻿using Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using Utility.Helper;

namespace BLL
{
    public class YX_BANK_QRCODE_BLL : BaseBLL
    {
        public void Insert(YX_BANK_QRCODE entity)
        {
            var sql = @"INSERT INTO YX_BANK_QRCODE(
                            ID,NAME,CODE,COMPANY_ID,
                            OPEAR_ID,OPEAR_NAME,OPEAR_DATE)
                        VALUES(
                            :ID,:NAME,:CODE,:COMPANY_ID,
                            :OPEAR_ID,:OPEAR_NAME,:OPEAR_DATE)";
            entity.ID = Guid.NewGuid().ToString();
            var parms = ParmsHelper.GetParms();
            parms.Add(":ID", entity.ID, "string");
            parms.Add(":NAME", entity.NAME, "string");
            parms.Add(":CODE", entity.CODE, "string");
            parms.Add(":COMPANY_ID", entity.COMPANY_ID, "int");
            parms.Add(":OPEAR_ID", entity.OPEAR_ID, "int");
            parms.Add(":OPEAR_NAME", entity.OPEAR_NAME, "string");
            parms.Add(":OPEAR_DATE", entity.OPEAR_DATE, "datetime");
            NoQuery(sql, parms);
        }

        public void Update(YX_BANK_QRCODE entity)
        {
            var sql = @"UPDATE YX_BANK_QRCODE
                        SET
                            NAME = :NAME,CODE = :CODE,COMPANY_ID = :COMPANY_ID,
                            OPEAR_ID = :OPEAR_ID,OPEAR_NAME = :OPEAR_NAME,
                            OPEAR_DATE = :OPEAR_DATE
                        WHERE
                            ID = :ID";
            var parms = ParmsHelper.GetParms();
            parms.Add(":ID", entity.ID, "string");
            parms.Add(":NAME", entity.NAME, "string");
            parms.Add(":CODE", entity.CODE, "string");
            parms.Add(":COMPANY_ID", entity.COMPANY_ID, "int");
            parms.Add(":OPEAR_ID", entity.OPEAR_ID, "int");
            parms.Add(":OPEAR_NAME", entity.OPEAR_NAME, "string");
            parms.Add(":OPEAR_DATE", entity.OPEAR_DATE, "datetime");
            NoQuery(sql, parms);
        }

        public void Delete(string id)
        {
            var sql = @"DELETE FROM YX_BANK_QRCODE WHERE ID = :ID";
            var parms = ParmsHelper.GetParms();
            parms.Add(":ID", id, "string");
            NoQuery(sql, parms);
        }

        public List<YX_BANK_QRCODE> GetDataByPage(int cmyId, int skip, int take)
        {
            var sql = @"SELECT
                            *
                        FROM
                            (SELECT
                                ROW_NUMBER() OVER(ORDER BY T1.NAME) AS R,
                                T1.*
                            FROM YX_BANK_QRCODE T1
                            WHERE
                                T1.COMPANY_ID = :N_COMPANY_ID) T1
                        WHERE
                            T1.R >= :START_NUMBER AND T1.R < :END_NUMBER";
            var parms = ParmsHelper.GetParms();
            parms.Add(":N_COMPANY_ID", cmyId, "int");
            parms.Add(":START_NUMBER", skip, "int");
            parms.Add(":END_NUMBER", skip + take, "int");
            var dt = Query(sql, parms);
            var entitys = new List<YX_BANK_QRCODE>();
            foreach (var item in dt.AsEnumerable())
            {
                var entity = new YX_BANK_QRCODE();
                entitys.Add(entity);
                entity.ID = item.GetValue<string>("ID");
                entity.NAME = item.GetValue<string>("NAME");
                entity.OPEAR_ID = item.GetValue<int?>("OPEAR_ID");
                entity.OPEAR_NAME = item.GetValue<string>("OPEAR_NAME");
                entity.CODE = item.GetValue<string>("CODE");
                entity.COMPANY_ID = item.GetValue<int>("COMPANY_ID");
                entity.OPEAR_DATE = item.GetValue<DateTime?>("OPEAR_DATE");
            }
            return entitys;
        }

        public int GetDataByCount(int cmyId)
        {
            var sql = @"SELECT
                            COUNT(1)
                        FROM YX_BANK_QRCODE T1
                        WHERE
                            T1.COMPANY_ID = :N_COMPANY_ID";
            var parms = ParmsHelper.GetParms();
            parms.Add(":N_COMPANY_ID", cmyId, "int");
            return GetCount(sql, parms);
        }

        public YX_BANK_QRCODE GetInfo(string id)
        {
            var sql = @"SELECT
                            T1.*
                        FROM YX_BANK_QRCODE T1
                        WHERE
                            T1.ID = :ID";
            var parms = ParmsHelper.GetParms();
            parms.Add(":ID", id, "string");
            var dt = Query(sql, parms);

            if (dt.Rows.Count == 0)
            {
                return null;
            }
            YX_BANK_QRCODE entity = new YX_BANK_QRCODE();
            entity.ID = dt.Rows[0].GetValue<string>("ID");
            entity.NAME = dt.Rows[0].GetValue<string>("NAME");
            entity.OPEAR_ID = dt.Rows[0].GetValue<int?>("OPEAR_ID");
            entity.OPEAR_NAME = dt.Rows[0].GetValue<string>("OPEAR_NAME");
            entity.CODE = dt.Rows[0].GetValue<string>("CODE");
            entity.COMPANY_ID = dt.Rows[0].GetValue<int>("COMPANY_ID");
            entity.OPEAR_DATE = dt.Rows[0].GetValue<DateTime?>("OPEAR_DATE");
            return entity;
        }
    }
}